package com.hellobike.haxing.modules.order.mapper;

import com.baomidou.mybatisplus.core.conditions.Wrapper;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.core.toolkit.Constants;
import com.hellobike.haxing.modules.order.entity.Order;
import com.hellobike.haxing.modules.order.entity.ShopStaticsVo;
import com.hellobike.haxing.modules.supplier.dto.SkuNum;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;

import java.time.LocalDateTime;
import java.util.Date;
import java.util.List;

/**
 * @Description: 订单信息
 * @Author: haxing-boot
 * @Date: 2020-02-08
 * @Version: V1.0
 */
public interface OrderMapper extends BaseMapper<Order> {
    /**
     * 分页查询
     *
     * @param page
     * @param queryWrapper
     * @return
     */
    IPage<Order> queryPage(IPage<Order> page, @Param(Constants.WRAPPER) Wrapper<Order> queryWrapper);

    /**
     * 查询下单基础信息
     *
     * @param id
     * @return
     */
    @Select("select o.*,d.m_phone as \"mphone\",d.receiver,u.mobile,u.nickname from t_order o,t_order_address d,t_user u where o.id=#{id} and o.id=d.order_id  and o.user_id=u.id")
    Order getOrderById(Long id);

    /**
     * 更新订单状态
     *
     * @param status
     */
    @Update("update t_order set status=#{status},update_time=now() where id=#{id} and deleted=0")
    void updateOrderStatus(@Param("id") Long id, @Param("status") Integer status);

    /**
     * 更新超时关闭
     *
     * @param date
     */
    @Update("update t_order set status=7,update_time=now(),pay_close_time=#{date} where #{date}>=create_time and deleted=0 and status=1 and is_pay=0")
    void updatePayTimeOutCloseOrder(Date date);

    @Select("select id from t_order where #{date}>=create_time and deleted=0 and status=1 and is_pay=0")
    List<Long> queryPayTimeOutCloseOrder(Date date);

    /**
     * 更新已同步采购单
     */
    void updateIsAsyncProcure(@Param("skuIds") List<Long> skuIds);

    /**
     * 修改订单已完成
     *
     * @param ids
     */
    void updateFinishOrder(@Param("ids") List<Long> ids);

    /**
     * 更新订单已返团长佣金
     *
     * @param orderIds
     */
    void updateShopCommission(@Param("ids") List<Long> orderIds);

    /**
     * 根据ID查询
     *
     * @param id
     * @return
     */
    @Select("select * from t_order o where o.id=#{id}")
    Order getById(Long id);

    /**
     * 当前用户下单数
     *
     * @param userId
     * @return
     */
    @Select("select count(0) from t_order o where  o.user_id=#{userId} and o.is_pay=1 and o.status>2")
    int countOrderUserId(Long userId);

    @Select("SELECT SUM(d.num) as c\n" +
            "FROM t_order t, t_order_detail d, t_product p, t_spu s\n" +
            "WHERE t.id = d.order_id\n" +
            "\tAND t.status = 3\n" +
            "\tAND d.product_id = p.id\n" +
            "\tAND t.success_time  between #{lastDay} and #{today}\n" +
            "\tAND p.spu_code = s.spu_code\n" +
            "\tAND s.stock_type = 0 and sku_id = #{skuId}\n" +
            "GROUP BY d.sku_id\n" +
            "ORDER BY d.sku_id ASC")
    Integer selectPurchaseCount(@Param("skuId") Long skuId, @Param("today") LocalDateTime today, @Param("lastDay") LocalDateTime lastDay);

    @Select("<script>" +
            "SELECT\n" +
            "\tsub.shopId,\n" +
            "\tsub.real_name,\n" +
            "\tsub.leadMobile,\n" +
            "\tsub.leadUser,\n" +
            "\tsub.productAmount,\n" +
            "\tsub.shopAmount,\n" +
            "\tsub.shop_type,\n" +
            "\tsub.mobile,\n" +
            "\t(\n" +
            "\tSELECT COUNT\n" +
            "\t\t( 1 ) \n" +
            "\tFROM\n" +
            "\t\tt_shop C \n" +
            "\tWHERE\n" +
            "\t\tC.lead_shop_id = sub.shopId \n" +
            "\t\tAND C.deleted = 0 \n" +
            "\t\tAND C.STATE IN ( 1, 2 ) \n" +
            "\t\t) AS shops,\n" +
            "\tCOALESCE((SELECT SUM\n" +
            "\t\t( od.product_amount ) AS subProductAmount \n" +
            "\tFROM\n" +
            "\t\tt_order om\n" +
            "\t\tJOIN t_order_detail od ON om.ID = od.order_id \n" +
            "\t\tAND om.leader_id = sub.shopId \n" +
            "\t\tAND om.status IN ( 3, 4, 5, 6 ) \n" +
            "\t),0) AS subProductAmount ,\n" +
            "\tCOALESCE((SELECT SUM\n" +
            "\t\t( od.leader_amount ) AS subLeaderAmount \n" +
            "\tFROM\n" +
            "\t\tt_order om\n" +
            "\t\tJOIN t_order_detail od ON om.ID = od.order_id \n" +
            "\t\tAND om.leader_id = sub.shopId \n" +
            "\t\tAND om.status IN ( 3, 4, 5, 6 ) \n" +
            "\t),0) AS subLeaderAmount\n" +
            "FROM\n" +
            "\t(\n" +
            "\tSELECT\n" +
            "\t\ts.ID AS shopId,\n" +
            "\t\ts.real_name,\n" +
            "\t\ts.mobile,\n" +
            "\tCASE\n" +
            "\t\t\t\n" +
            "\t\t\tWHEN s.shop_type = 1 THEN\n" +
            "\t\t\t'团长' ELSE'推广员' \n" +
            "\t\tEND AS shop_type,\n" +
            "\t\tCOALESCE ( b.real_name, '-' ) AS leadUser,\n" +
            "\t\tCOALESCE ( b.mobile, '-' ) AS leadMobile,\n" +
            "\t\tSUM ( COALESCE ( od.product_amount, 0 ) ) AS productAmount,\n" +
            "\t\tSUM ( COALESCE ( od.shop_amount, 0 ) ) AS shopAmount \n" +
            "\tFROM\n" +
            "\t\tt_shop s\n" +
            "\t\tLEFT JOIN t_shop b ON s.lead_shop_id = b.\n" +
            "\t\tID LEFT JOIN t_order o ON o.shop_id = s.\n" +
            "\t\tID LEFT JOIN t_order_detail od ON od.order_id = o.ID \n" +
            "\t\tAND o.status IN ( 3, 4, 5, 6 ) \n" +
            "\t\tAND od.deleted = 0 \n" +
            "\tWHERE\n" +
            "\t\ts.shop_type IN ( 1, 2 ) \n" +
            "\tGROUP BY\n" +
            "\t\ts.ID,\n" +
            "\t\ts.real_name,\n" +
            "\t\ts.mobile,\n" +
            "\t\ts.shop_type,\n" +
            "\t\tb.real_name,\n" +
            "\tb.mobile \n" +
            "\t) sub" +
            "<where><if test = \"vo.mobile != null and vo.mobile != ''\"> and  sub.mobile = #{vo.mobile} </if>" +
            "<if test = \"vo.realName != null and vo.realName != ''\"> and  sub.real_name like '%' || #{vo.realName} || '%' </if>" +
            "<if test = \"vo.shopType != null\"> and  sub.shopType  = #{vo.realName}  </if>" +
            "</where></script>" )
    IPage<ShopStaticsVo> selectShopStatics(IPage<ShopStaticsVo> page, @Param("vo") ShopStaticsVo vo);

    @Select("select o.id from t_order o  where create_time >  CURRENT_DATE - interval '1 hour'\n" +
            "and pay_time is not null and status in (3) and not exists (select  * from t_purchase_pro_record pr \n" +
            "where pr.order_code = o.bill_no) and pay_time < now() - interval '1 MINUTE'")
    List<Long> selectFixedPoOrderId();

    @Select("SELECT b.sku_id as sku_id,sum(b.num) as c from t_order a join t_order_detail b \n" +
            "on a.id=b.order_id \n" +
            "and a.create_time > current_date - 1 and a.success_time > CURRENT_DATE\n" +
            "and a.status in (3,4)\n" +
            "group by b.sku_id")
    List<SkuNum> selectSkuNum();
}
